package DB;

import UI.Questionlib;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TestLib {
    public static int addQuestion(String question, String type, String section, String topic, String answer) throws SQLException {
        String addSQL = "insert into QuestionLib" +
                "(question, type, section, topic, answer)" +
                "values" +
                "(?,?,?,?,?)";
        Connection connection = JDBC.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(addSQL,Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, question);
        preparedStatement.setString(2, type);
        preparedStatement.setString(3, section);
        preparedStatement.setString(4, topic);
        preparedStatement.setString(5, answer);
        int a=preparedStatement.executeUpdate();
        System.out.println(a);
        ResultSet result=preparedStatement.getGeneratedKeys();
        int id=0;
        if(result.next()) {
            id = result.getInt(1);
        }
        result.close();
        preparedStatement.close();
        JDBC.close(connection);

        return id;
    }

    public static void deleteQuestion(int id) throws SQLException {
        String deleteSQL="delete from QuestionLib " +
                "where id=?";
        Connection connection=JDBC.getConnection();
        PreparedStatement preparedStatement=connection.prepareStatement(deleteSQL);
        preparedStatement.setInt(1,id);
        preparedStatement.executeUpdate();

        preparedStatement.close();
        JDBC.close(connection);

    }

    public static void modifyQuestion(int id, String question, String type, String section, String topic, String answer) throws SQLException{
        String modifySQL="update QuestionLib " +
                "set question=?,type=?,section=?,topic=?,answer=? where id=?";
        Connection connection=JDBC.getConnection();
        PreparedStatement preparedStatement=connection.prepareStatement(modifySQL);
        preparedStatement.setString(1,question);
        preparedStatement.setString(2,type);
        preparedStatement.setString(3,section);
        preparedStatement.setString(4,topic);
        preparedStatement.setString(5,answer);
        preparedStatement.setInt(6,id);
        preparedStatement.executeUpdate();

        preparedStatement.close();
        JDBC.close(connection);

    }

    public static Questionlib selectQuestion(int id) throws SQLException {
        String selectSQL="select * from QuestionLib " +
                "where id=?";
        Connection connection=JDBC.getConnection();
        PreparedStatement preparedStatement=connection.prepareStatement(selectSQL);
        preparedStatement.setInt(1,id);
        ResultSet result=preparedStatement.executeQuery();
        Questionlib questionlib=new Questionlib();
        questionlib.setId(id);
        while(result.next()){
            questionlib.setQuestion(result.getString("question"));
            questionlib.setType(result.getString("type"));
            questionlib.setSection(result.getString("section"));
            questionlib.setTopic(result.getString("topic"));
            questionlib.setAnswer(result.getString("answer"));
        }

        result.close();
        preparedStatement.close();
        JDBC.close(connection);
        return questionlib;

    }

    public static List<Questionlib> getQuestions() throws SQLException {
        String sql="select * from QuestionLib";
        List<Questionlib> questions=new ArrayList<>();
        Connection connection=JDBC.getConnection();
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        ResultSet result = preparedStatement.executeQuery();
        while (result.next()){
            Questionlib questionlib=new Questionlib();
            questionlib.setId(result.getInt("id"));
            questionlib.setQuestion(result.getString("question"));
            questionlib.setType(result.getString("type"));
            questionlib.setSection(result.getString("section"));
            questionlib.setTopic(result.getString("topic"));
            questionlib.setAnswer(result.getString("answer"));
            questions.add(questionlib);
        }
        result.close();
        preparedStatement.close();
        JDBC.close(connection);
        return questions;
    }

    public static List<Questionlib> selectTopicQuestions(String topic) throws SQLException {
        List<Questionlib> questionlibs=new ArrayList<>();
        String sql="select *\n" +
                "from questionlib\n" +
                "where topic=?";
        Connection connection=JDBC.getConnection();
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        preparedStatement.setString(1,topic);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            Questionlib questionlib = new Questionlib();
            questionlib.setId(resultSet.getInt("id"));
            questionlib.setQuestion(resultSet.getString("question"));
            questionlib.setType(resultSet.getString("type"));
            questionlib.setSection(resultSet.getString("section"));
            questionlib.setTopic(resultSet.getString("topic"));
            questionlib.setAnswer(resultSet.getString("answer"));
            questionlibs.add(questionlib);
        }
        resultSet.close();
        preparedStatement.close();
        JDBC.close(connection);
        return questionlibs;
    }

    public static List<Questionlib> selectSectionQuestions(String section) throws SQLException {
        List<Questionlib> questionlibs=new ArrayList<>();
        String sql="select * " +
                "from questionlib " +
                "where section=?";
        Connection connection=JDBC.getConnection();
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        preparedStatement.setString(1,section);
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            Questionlib questionlib = new Questionlib();
            questionlib.setId(resultSet.getInt("id"));
            questionlib.setQuestion(resultSet.getString("question"));
            questionlib.setType(resultSet.getString("type"));
            questionlib.setSection(resultSet.getString("section"));
            questionlib.setTopic(resultSet.getString("topic"));
            questionlib.setAnswer(resultSet.getString("answer"));
            questionlibs.add(questionlib);
        }
        resultSet.close();
        preparedStatement.close();
        JDBC.close(connection);
        return questionlibs;
    }

}
